package com.shujia.spark.sql.test

import org.apache.spark.sql.{Column, DataFrame, SparkSession}

object Test03 {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession
      .builder()
      .master("local")
      .appName("test")
      .getOrCreate()
    //导入隐式转换
    import spark.implicits._
    //导入spark 所有的函数
    import org.apache.spark.sql.functions._
    //读取两张表
    val t1DF: DataFrame = spark.read
        .schema("name string,subject string,score string")
      .csv("data/test/t1.txt")
    t1DF.show()
    t1DF.printSchema()


    val t2DF: DataFrame = spark.read
        .schema("name String,math String,english String")
      .csv("data/test/t2.txt")

    t2DF.show
    t1DF.createOrReplaceTempView("t1")
    t2DF.createOrReplaceTempView("t2")
    /**
      * sql
      */
    //行转列
    spark.sql(
      """
         select name,
         max(case subject when '数学' then score else 0 end)math,
         max(case subject when '英语' then score else 0 end)english
         from t1
         group by name
      """.stripMargin).show()

    //列转行
    spark.sql(
      """
        |select name,
        |explode(map('数学',math,'英语',english)) as (subject,score7)
        |from t2
        |
      """.stripMargin).show()


  }
}
